/*
 * InsertoDAO.java
 *
 * Created on 8 de marzo de 2007, 04:03 PM
 *
 * To change this template, choose Tools | Options and locate the template under
 * the Source Creation and Management node. Right-click the template and choose
 * Open. You can then make changes to the template in the Source Editor.
 */

package com.src.computec.radar.services.utilities.dao;

import com.src.computec.radar.base.dao.GenericDAO;
import com.src.computec.radar.base.dao.exception.DAOException;
import com.src.computec.radar.services.base.constantes.ConstantesGenerales;
import com.src.computec.radar.services.utilities.dto.EnvioDto;
import com.src.computec.radar.services.utilities.dto.InsertoDto;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 *
 * @author aocampo
 */
public class InsertoDAO extends GenericDAO implements BaseInsertoDAO {
    
    ConstantesGenerales constantesGenerales = ConstantesGenerales.getInstance(); 
    
    /** Creates a new instance of InsertoDAO */
    public InsertoDAO() {
    }
    
    public List getInsertosByEnvio(EnvioDto dto) throws DAOException {
        List listInsertos = new ArrayList();
        Connection con = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            con = getConnectionDB2();
            statement = con.prepareStatement("SELECT IE.CODIGO_BARRAS, IE.CODIGO_INSERTO, IE.NUMERO_ORDEN, I.DESCRIPCION_INSERTO " +
                                             "FROM " + constantesGenerales.SCHEMA + ".INSERTO_ENVIO IE " +
                                             "INNER JOIN " + constantesGenerales.SCHEMA + ".INSERTO I ON IE.CODIGO_INSERTO = I.CODIGO_INSERTO AND IE.NUMERO_ORDEN = I.NUMERO_ORDEN " +
                                             "WHERE IE.CODIGO_BARRAS = ?");
            int indice = 0;
            statement.setObject(++indice, dto.getCodigoBarras());
            rs = statement.executeQuery();
            while (rs.next()) {
                indice = 0;
                InsertoDto insertoDto = new InsertoDto();
                insertoDto.setCodigoBarras(rs.getString(++indice));
                insertoDto.setCodigoInserto(rs.getString(++indice));
                insertoDto.setNumeroOrden(rs.getString(++indice));
                insertoDto.setDescripcionInserto(rs.getString(++indice));
                listInsertos.add(insertoDto);
            }
        } 
        catch (SQLException e) {
            throw new DAOException("InsertoDAO:getInsertosByEnvio: " + e.toString(), e);
        }
        finally {
            closeConnection(con, statement, rs);
        }
        return listInsertos;
    }
    
    public List getInsertosByOrdenProceso(InsertoDto dto) throws DAOException 
    {
        List listInsertos = new ArrayList();
        Connection con = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            con = getConnectionDB2();
            statement = con.prepareStatement("SELECT NUMERO_ORDEN, CODIGO_INSERTO, DESCRIPCION_INSERTO, CODIGO_TIPO_TARIFA, JERARQUIA_CORREO " +
                                             "FROM " + constantesGenerales.SCHEMA + ".INSERTO " +
                                             "WHERE NUMERO_ORDEN = ? " +
                                             "ORDER BY DESCRIPCION_INSERTO ASC ");
            
            int indice = 0;
            statement.setObject(++indice, dto.getNumeroOrden());
            rs = statement.executeQuery();
            
            while (rs.next()) 
            {
                int indice1 = 0;
                InsertoDto insertoDto = new InsertoDto();
                insertoDto.setNumeroOrden(rs.getString(++indice1));
                insertoDto.setCodigoInserto(rs.getString(++indice1));
                insertoDto.setDescripcionInserto(rs.getString(++indice1));
                insertoDto.setCodigoTipoTarifa(rs.getString(++indice1) != null? rs.getString(indice1):"_");
                insertoDto.setJerarquiaCorreo(rs.getString(++indice1) != null? rs.getString(indice1):"_");
                
                listInsertos.add(insertoDto);
            }
        } 
        catch (SQLException e) 
        {
            throw new DAOException("InsertoDAO:getInsertosByOrdenProceso: " + e.toString(), e);
        }
        finally 
        {
            closeConnection(con, statement, rs);
        }
        return listInsertos;
    }
    
    public void updateInsertos(List insertos) throws DAOException 
    {
        Connection con = null;
        PreparedStatement statement = null;        
        
        try {
            con = getConnectionDB2();
            
            statement = con.prepareStatement("UPDATE " + constantesGenerales.SCHEMA + ".INSERTO SET CODIGO_TIPO_TARIFA = ?, JERARQUIA_CORREO = ?, DESCRIPCION_INSERTO = ? " +
                                             "WHERE NUMERO_ORDEN = ? AND CODIGO_INSERTO = ? ");
                     
            Iterator iterInsertos = insertos.iterator();
            
            while(iterInsertos.hasNext())
            {
                int indice = 0;
                InsertoDto insertoDto = (InsertoDto)iterInsertos.next();
                
                statement.setObject(++indice, insertoDto.getCodigoTipoTarifa());
                statement.setObject(++indice, insertoDto.getJerarquiaCorreo());
                statement.setObject(++indice, insertoDto.getDescripcionInserto());
                statement.setObject(++indice, insertoDto.getNumeroOrden());
                statement.setObject(++indice, insertoDto.getCodigoInserto());
                statement.executeUpdate();
            }           
        } 
        catch (SQLException e) 
        {
            throw new DAOException("InsertosAdminDAO:updateInsertos: " +e.toString(), e);
        } 
        finally 
        {
            closeConnection(con, statement);
        }        
    }
}